//导入数据库操作模块：
const { result } = require('@hapi/joi/lib/base')
const db = require('../db/index')

//导入密码加密模块
const bcrypt = require('bcryptjs')

//读者信息查询
exports.selectAllreader = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const userinfo = req.user
    //定义查询总数sql
    let sql1 = `SELECT COUNT(*) AS total FROM user WHERE role=2`
    db.get(sql1, [], (err, result) => {
        if (err) return res.cc(err)
        const total = result.total

        const sql = `SELECT id, username FROM user WHERE role=2 ORDER BY id LIMIT ${req.body.size} OFFSET ${(req.body.currentPage - 1) * req.body.size}`
        db.all(sql, [], (err, rows) => {
            if (err) return res.cc(err)
            res.send({
                status: 0,
                message: '读者信息查询成功！',
                data: rows,
                total: total
            })
        })
    })
}

//查看借出图书
exports.selectAlllendBook = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const userinfo = req.user
    //定义查询总数sql
    let sql1 = `SELECT COUNT(*) AS total FROM borrowing`
    db.get(sql1, [], (err, result) => {
        if (err) return res.cc(err)
        const total = result.total

        const sql = `SELECT * FROM borrowing ORDER BY id LIMIT ${req.body.size} OFFSET ${(req.body.currentPage - 1) * req.body.size}`
        db.all(sql, [], (err, rows) => {
            if (err) return res.cc(err.message)
            if (rows.length === 0) {
                res.send({
                    status: 0,
                    message: "没有借出的图书!",
                    data: rows
                })
            } else {
                res.send({
                    status: 0,
                    message: '获取借阅记录成功!',
                    data: rows,
                    total: total
                })
            }
        })
    })
}

//图书催还
exports.bookrecall = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const sql = `UPDATE borrowing SET urgerepayment=0 WHERE id=${req.body.id}`
    db.run(sql, function(err) {
        if (err) return res.cc(err.message)
        if (this.changes !== 1) return res.cc('催还失败！')
        res.cc('催还成功！', 0)
    })
}

//读者信息修改
exports.Readerinformation = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const userinfo = req.body

    //定义查询SQL语句
    const sql = `SELECT * FROM user WHERE id=${userinfo.id}`
    db.get(sql, [], function (err, row) {
        if (err) {
            return res.cc(err.message)
        }
        if (!row) {
            return res.cc('用户不存在!')
        }

        // 对用户的密码进行 bcrypt 加密
        userinfo.password = bcrypt.hashSync(userinfo.password, 10)

        //定义更新用户的 SQL 语句：
        const updateSql = 'UPDATE user SET username=?, password=? WHERE id=?;'
        db.run(updateSql, [userinfo.username, userinfo.password, userinfo.id], function (err) {
            if (err) return res.cc(err.message)
            if (this.changes !== 1) {
                return res.cc('修改信息失败，请稍后再试！')
            }
            res.cc('修改成功！', 0)
        })
    })
}

//将普通用户修改为管理员
exports.addadmin = (req, res) => {
    if(req.user.role!== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const userinfo = req.body
    //定义查询SQL语句
    const sql = `SELECT * FROM user WHERE id=${userinfo.id}`
    db.get(sql, [], function (err, row) {
        if (err) {
            return res.cc(err.message)
        }
        if (!row) {
            return res.cc('用户不存在!')
        }
        //定义更新用户role的 SQL 语句：
        const updateSql = 'UPDATE user SET role=1 WHERE id=?;'
        db.run(updateSql, [userinfo.id], function (err) {
            if (err) return res.cc(err.message)
            if (this.changes!== 1) {
                return res.cc('添加管理员失败，请稍后再试！')
            }
            res.cc('添加成功！', 0)
        })
    })
}

//读者删除
exports.deleteuser = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const sql = `DELETE FROM user WHERE id=${req.body.id}`
    db.run(sql, function(err) {
        if (err) return res.cc(err.message)
        if (this.changes !== 1) return res.cc('用户删除失败！')
        res.cc('删除成功!', 0)
    })
}


//查询所有借阅记录
exports.Allborrowingrecords = (req, res) => {
    if (req.user.role !== 1) {
        return res.cc("你不是管理员，无此权限!");
    }
    const userinfo = req.body;
    //定义查询总数sql
    let sql1 = `SELECT COUNT(*) AS total FROM borrowinghistory`;
    db.get(sql1, (err, result) => {
        if (err) return res.cc(err);
        const total = result.total;

        //定义分页查询sql，SQLite使用 LIMIT ? OFFSET ?
        let sql2 = 'SELECT * FROM borrowinghistory LIMIT ? OFFSET ?';

        // 注意：SQLite中的分页计算方式与MySQL略有不同，需要手动计算偏移量
        db.all(sql2, [userinfo.size, (userinfo.currentPage - 1) * userinfo.size], (err, result) => {
            if (err) return res.cc(err);

            if (result.length === 0) {
                res.send({
                    status: 0,
                    message: "没有借阅记录！",
                    data: result
                });
            } else {
                res.send({
                    status: 0,
                    message: "借阅记录查询成功！",
                    data: {
                        result,
                        total: total
                    }
                });
            }
        });
    });
}


//修改图书
exports.modifyBook = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const userinfo = req.body
    const keys = Object.keys(userinfo).filter(key => key !== 'id').join(', ')
    const values = Object.values(userinfo).filter((_, index) => index !== 0)
    const placeholders = values.map(() => '?').join(', ')
    const sql = `UPDATE book SET (${keys}) = (${placeholders}) WHERE id = ${userinfo.id}`
    console.log('Executing SQL:', sql);
    db.run(sql, values, function(err) {
        if (err) return res.cc(sql)
        res.send({
            status: 0,
            message: "修改图书成功!",
        })
    })
}

//删除图书
exports.delectBook = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const userinfo = req.body
    const sql = `DELETE FROM book WHERE id = ${userinfo.id}`
    db.run(sql, function(err) {
        if (err) return res.cc(err)
        if (this.changes == 0) return res.cc('书籍不存在！')
        res.send({
            status: 0,
            message: "删除图书成功!",
        })
    })
}

//书籍借出排行
exports.Lendingranking = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const sql = `SELECT bookname, COUNT(*) AS count FROM borrowinghistory GROUP BY bookname ORDER BY count DESC`
    db.all(sql, [], (err, rows) => {
        if (err) return res.cc(err)
        res.send({
            status: 0,
            message: "书籍借出排行查询成功!",
            data: rows
        })
    })
}

//添加图书
exports.addBook = (req, res) => {
    if(req.user.role !== 1){
        return res.cc("你不是管理员，无此权限!")
    }
    const userinfo = req.body
    const sql = `INSERT INTO book(id, bookname, author, introduce, price, inventory,category) VALUES (?, ?, ?, ?, ?, ?, ?)`
    db.run(sql, [userinfo.id,userinfo.bookname, userinfo.author, userinfo.introduce, userinfo.price, userinfo.inventory, userinfo.category], function(err) {
        if (err) return res.cc(err)
        res.send({
            status: 0,
            message: "添加成功!",
        })
    })
}